package com.core.common.utils;

import com.core.common.exception.BDException;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.*;

/**
 * @program: gims
 * @description:
 * @author: hl
 * @create: 2024-05-30 18:19
 **/
@Slf4j
public class ExcelUtil {



    public static List<Map<String, Object>> parse(MultipartFile file, String[] tableTitles) {
        List<Map<String, Object>> result = new ArrayList<>();
        if (null == file) {
            throw new BDException(600, "请上传XLSX格式文件");
        }
        String originalFilename = file.getOriginalFilename();
        if (originalFilename.indexOf(".xlsx") == -1) {
            throw new BDException(600, "请上传XLSX格式文件");
        }
        try(InputStream fis= file.getInputStream()) {
            if (file.getOriginalFilename().toLowerCase(Locale.ROOT).contains(".xlsx")) {
                result.clear();
                result.addAll(parseXLSX(fis,tableTitles));
            } else {
                throw new BDException(600, "请上传XLSX格式文件");
            }
        } catch (Exception e) {
          e.printStackTrace();
        }
        return result;
    }


    private static List<Map<String, Object>> parseXLSX(InputStream fis, String[] tableTitles) {
        List<Map<String, Object>> datas = new ArrayList<>();
        ;
        try (XSSFWorkbook book = new XSSFWorkbook(fis);) {
            XSSFSheet sheet = book.getSheetAt(0);
            if (sheet.getLastRowNum() < 0) {
                return datas;
            }
            if (null == tableTitles || tableTitles.length == 0) {
                XSSFRow rowTitle = sheet.getRow(0);
                tableTitles = new String[rowTitle.getLastCellNum() + 1];
                for (int i = 0; i < rowTitle.getLastCellNum(); i++) {
                    XSSFCell cell = rowTitle.getCell(i);
                    tableTitles[i] = cell.getStringCellValue();
                }
            }
            if (sheet.getLastRowNum() + 1 != tableTitles.length) {
                throw new BDException(600, "表头列数与数据列数不匹配");
            }
            for (int sheetIndex = 1; sheetIndex < sheet.getLastRowNum(); sheetIndex++) {
                XSSFRow row = sheet.getRow(sheetIndex);
                Map<String, Object> data = new HashMap<>();
                if (row == null) {
                    continue;
                }
                for (int i = 0; i < row.getLastCellNum(); i++) {
                    XSSFCell cell = row.getCell(i);
                    if (CellType.NUMERIC.equals(cell.getCellType())) {
                        data.put(tableTitles[i], cell.getNumericCellValue());
                    }
                    data.put(tableTitles[i], cell.getStringCellValue());
                }
                datas.add(data);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return datas;
    }


    @lombok.SneakyThrows
    public static void downloadXLSX(HttpServletResponse response,  String[] tableName,
                                    List<Map<String, Object>> datas) {
        downloadXLSX(response,"导出数据",tableName,datas);
    }

    /**
     * 导出.xlsx格式excel文件
     * @param response
     * @param fileName
     * @param tableName
     */
    @lombok.SneakyThrows
    public static void downloadXLSX(HttpServletResponse response, String fileName, String[] tableName,
                                    List<Map<String, Object>> datas) {
        Workbook workbook = generateXLSX(datas, tableName);
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Access-Control-Expose-Headers","Content-Disposition");
        response.setHeader("Content-disposition", "attachment; filename="
                + URLEncoder.encode(fileName,"utf-8") + ".xlsx");
        try (OutputStream ouputStream=response.getOutputStream()){

            workbook.write(ouputStream);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 组装 Excel 对象
     * @param datas
     * @return
     */
    private static XSSFWorkbook generateXLSX(List<Map<String, Object>> datas,  String[] tableTitles) {
        XSSFWorkbook book = new XSSFWorkbook();
        try {
            XSSFSheet sheet = book.createSheet();
            sheet.autoSizeColumn(1, true);// 自适应列宽度
            int rowIndex=0;
            if (tableTitles != null && tableTitles.length > 0){
                // 填充表头标题
                XSSFRow firstRow = sheet.createRow(rowIndex++);// 第几行（从0开始）
                for (int i = 0; i < tableTitles.length; i++) {
                    firstRow.createCell(i).setCellValue(tableTitles[i]);
                }
            }
            // 填充表格内容
            if (datas != null && !datas.isEmpty()) {
                for (Map<String, Object> data : datas) {
                    XSSFRow row = sheet.createRow(rowIndex++);// index：第几行
                    for (int i = 0; i < tableTitles.length; i++) {
                        XSSFCell cell = row.createCell(i);// 第几列：从0开始
                        //设置单元格内容为字符串型
                        cell.setCellType(CellType.STRING);
                        cell.setCellValue(data.get(tableTitles[i]).toString());
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return book;
    }


}
